/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dal;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;

/**
 *
 * @author Stanley
 */
public class BaiThiDAO {

    public int getSoLuongBaiThi(int maDT, String maTS) {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "select count(*) as Soluong from baithi "
                    + " where dethi = " + Integer.toString(maDT) + " and thisinh = " + maTS;
            ResultSet rs = query.executeQuery(sql);
            rs.next();
            return rs.getInt("Soluong");
        } catch (Exception e) {
            return -1;
        }
    }

    public ResultSet getBaiThi(String maBT) {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "SELECT * FROM ( (baithi left join thisinh on thisinh = maTS) "
                    + " left join dethi on dethi = maDT ) left join loaidethi on loaidethi = maLDT  WHERE maBT = " + maBT;

            return query.executeQuery(sql);
        } catch (Exception e) {
            return null;
        }
    }

    public ResultSet getChiTietBaiThi(String maBT) {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "select * from ct_baithi CTBT join baithi BT on CTBT.baithi = BT.maBT "
                    + " join ct_dethi CTDT on (BT.dethi = CTDT.dethi and CTBT.cauhoi = CTDT.cauhoi) "
                    + " join cauhoi CH on CTDT.cauhoi = CH.maCH where CTBT.baithi = " + maBT
                    + " order by CTDT.thutu asc";

            return query.executeQuery(sql);
        } catch (Exception e) {
            return null;
        }
    }

    public ResultSet getBaiThiSauKhiThi(String maBT) {
//        query.executeQuery("select * from baithi BT join ct_dethi CTDT on BT.dethi = CTDT.dethi join cauhoi CH on CTDT.cauhoi = CH.maCH "
//            + " where maBT = " + maBT);
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "select * from baithi BT join ct_dethi CTDT on BT.dethi = CTDT.dethi join cauhoi CH on CTDT.cauhoi = CH.maCH "
                    + " where maBT = " + maBT;

            return query.executeQuery(sql);
        } catch (Exception e) {
            return null;
        }
    }

    public ResultSet getBaiThi(String maDT, int maTS, String time) {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "select * from baithi where dethi = " + maDT
                    + " and thisinh = " + Integer.toString(maTS)
                    + " and thoigianbd = '" + time + "'";

            return query.executeQuery(sql);
        } catch (Exception e) {
            return null;
        }
    }

    public boolean insertBaiThi(String maDT, int maTS, String time) {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "insert into baithi (dethi, thisinh, thoigianbd, thoigiankt, diem) values ( "
                    + maDT + " , " + Integer.toString(maTS) + " , '" + time + "' , '" + time + "' , -1 )";
            int result = query.executeUpdate(sql);
            return result == 0 ? false : true;
        } catch (Exception e) {
            return false;
        }

    }

    public int getThoiGianQuyDinh(String maBT) {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "select * from baithi join dethi on dethi = maDT join loaidethi on loaidethi = maLDT where maBT = " + maBT;
            ResultSet rs = query.executeQuery(sql);
            rs.next();
            return rs.getInt("thoigian");
        } catch (Exception e) {
            return -1;
        }
    }

    public Timestamp getThoiGianBatDau(String maBT) {
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "select * from baithi join dethi on dethi = maDT join loaidethi on loaidethi = maLDT where maBT = " + maBT;
            ResultSet rs = query.executeQuery(sql);
            rs.next();
            return rs.getTimestamp("thoigianbd");
        } catch (Exception e) {
            return null;
        }
    }

    public boolean updateThoiGianKetThuLamBai(String maBT, String thoigiankt, float diem) {
//        query.executeUpdate("update baithi set thoigiankt = '" + sdf.format(thoigiankt) + "', diem = " + diem + " where maBT = " + maBT);
        Connection connection = SqlDatabase.getInstance();
        try {
            Statement query = (Statement) connection.createStatement();
            String sql = "update baithi set thoigiankt = '" + thoigiankt + "', diem = " + diem + " where maBT = " + maBT;
            int result = query.executeUpdate(sql);
            return result == 0 ? false : true;
        } catch (Exception e) {
            return false;
        }
    }
}
